# Copyright© 2021 谈翰文
#
#         This program is free software: you can redistribute it and/or modify
#         it under the terms of the GNU General Public License as published by
#         the Free Software Foundation, either version 3 of the License, or
#         (at your option) any later version.
#
#         This program is distributed in the hope that it will be useful,
#         but WITHOUT ANY WARRANTY; without even the implied warranty of
#         MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#         GNU General Public License for more details.
#
#         You should have received a copy of the GNU General Public License
#         along with this program.  If not, see <https://www.gnu.org/licenses/>.
import datetime
import sqlite3
import tkinter.messagebox as messagebox
from tkinter import *
from tkinter.ttk import Treeview

import instructions_support

# 获得当前日期
curTime = datetime.datetime.now()
month = curTime.month if curTime.month >= 10 else '0' + str(curTime.month)
day = curTime.day if curTime.day >= 10 else '0' + str(curTime.day)
curDate = "%s%s%s" % (curTime.year, month, day)
# 两个要用到的颜色
color1 = "#FFFFFF"  # 纯白
color2 = "#AD1453"  # 罗马色


# 显示位置
def clac_geo(Root, width, height):
    screenwidth = Root.winfo_screenwidth()  # 屏幕宽
    screenheight = Root.winfo_screenheight()  # 屏幕高
    # 前两项是尺寸，后两项是坐标
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2)
    return alignstr


def vp_start_gui():
    '''Starting point when module is the main routine.'''
    global val, w, root
    root = Tk()
    top = data(root)
    instructions_support.init(root, top)
    root.mainloop()


w = None


def create_data(root, *args, **kwargs):
    '''Starting point when module is imported by another program.'''
    global w, w_win, rt
    rt = root
    w = Toplevel(root)
    top = data(w)
    instructions_support.init(w, top, *args, **kwargs)
    return (w, top)


def destroy_data():
    global w
    w.destroy()
    w = None


class data:
    def __init__(self, top=None):
        '''This class configures and populates the toplevel window.
           top is the toplevel containing window.'''
        _bgcolor = '#d9d9d9'  # X11 color: 'gray85'
        _fgcolor = '#000000'  # X11 color: 'black'
        _compcolor = '#d9d9d9'  # X11 color: 'gray85'
        _ana1color = '#d9d9d9'  # X11 color: 'gray85'
        _ana2color = '#ececec'  # Closest X11 color: 'gray92'
        font10 = "-family {Segoe UI} -size 16 -weight normal -slant " \
                 "roman -underline 1 -overstrike 0"
        font12 = "-family {Segoe UI} -size 24 -weight bold -slant " \
                 "roman -underline 0 -overstrike 0"

        top.geometry("600x450+367+127")
        top.title("共享单车管理系统")
        top.iconphoto(False, PhotoImage(file='icon.png'))
        top.configure(background="#d9d9d9")
        # menubar
        menubar = Menu(top)
        top.configure(menu=menubar)

        helpmenu = Menu(menubar, tearoff=0)
        menubar.add_cascade(label='帮助', menu=helpmenu)
        helpmenu.add_command(label='操作说明', command=self.description)
        helpmenu.add_command(label='授权信息', command=self.about)

        self.Frame1 = Frame(top)
        self.Frame1.place(relx=-0.033, rely=-0.089, relheight=1.202
                          , relwidth=1.065)
        self.Frame1.configure(relief='groove')
        self.Frame1.configure(borderwidth="2")
        self.Frame1.configure(relief='groove')
        self.Frame1.configure(background="#ffffff")
        self.Frame1.configure(highlightbackground="#d9d9d9")
        self.Frame1.configure(highlightcolor="black")
        self.Frame1.configure(width=639)

        self.Label2 = Label(self.Frame1)
        self.Label2.place(relx=0.419, rely=0.166, height=36, width=114)
        self.Label2.configure(background="#ffffff")
        self.Label2.configure(disabledforeground="#a3a3a3")
        self.Label2.configure(font=font10)
        self.Label2.configure(foreground="#000000")
        self.Label2.configure(text='''功能选择''')
        # 选择盘
        self.labelFrame2 = LabelFrame(top, bg="#FFFFFF")
        self.labelFrame2.pack(expand='yes')
        buttonIncome = Button(self.labelFrame2, text='各车的收入记录', bg=color2, fg=color1, width=12, height=1,
                              command=lambda: self.DBShow("BIKE_INCOME"))  # 注意通过command传参的方式
        buttonIncome.grid(row=1, column=0, padx=35, pady=15)

        # 车状态
        buttonState = Button(self.labelFrame2, text='车状态', bg=color2, fg=color1, width=12, height=1,
                             command=lambda: self.DBShow("BIKE_STATE"))
        buttonState.grid(row=1, column=1, padx=35, pady=15)

        # 车定位
        buttonLoc = Button(self.labelFrame2, text='车定位', bg=color2, fg=color1, width=12, height=1,
                           command=lambda: self.DBShow("BIKE_LOC"))
        buttonLoc.grid(row=2, column=0, padx=35, pady=15)

        # 区域收入记录
        buttonAI = Button(self.labelFrame2, text='区域收入记录', bg=color2, fg=color1, width=12, height=1,
                          command=lambda: self.DBShow("LOC_INCOME"))
        buttonAI.grid(row=3, column=0, padx=35, pady=15)

        # 客户的信息
        buttonCustomer = Button(self.labelFrame2, text='客户的信息', bg=color2, fg=color1, width=12, height=1,
                                command=lambda: self.DBShow("CUSTOMER_INFO"))
        buttonCustomer.grid(row=3, column=0, padx=35, pady=15)

    def description(self):
        messagebox.showinfo('操作说明', '选择以下按钮，进入相应子系统进行操作。 ')  # 显示对话框

    def about(self):
        messagebox.showinfo('LICENSE',
                            'Copyright© 2021 谈翰文 \n '
                            '\n                                            '
                            'This program is free software: you can redistribute it and/or modify \n'
                            'it under the terms of the GNU General Public License as published by \n'
                            'the Free Software Foundation, either version 3 of the License. \n'
                            '\n'
                            'This program is distributed in the hope that it will be useful, \n'
                            'but WITHOUT ANY WARRANTY; without even the implied warranty of \n'
                            'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the \n'
                            'GNU General Public License for more details. \n'
                            '\n'
                            'You should have received a copy of the GNU General Public License\n'
                            'along with this program.  If not, see <https://www.gnu.org/licenses/>.')  # 显示对话框

    def readTreeViewAction(self, rt, rowList):
        tv = Treeview(rt, height=18, show="headings", columns=self.columnsList)
        for c in self.columnsList:
            tv.column(c, width=100, anchor='center')  # 表示列,不显示
            tv.heading(c, text=c)  # 显示表头
        scrollBar1 = Scrollbar(rt, orient=VERTICAL, command=tv.yview)  # 滚动条
        tv.configure(yscrollcommand=scrollBar1.set)
        tv.grid(row=0, column=0, sticky=NSEW)
        scrollBar1.grid(row=0, column=1, sticky=NS)

        # 插入数据
        for row in rowList:
            valueList = [row[i] for i in range(len(self.columnsList))]
            tv.insert('', 0, values=valueList)

        return tv

    def DBShow(self, tableName):
        self.tableName = tableName  # 获取表名
        # 连接数据库
        self.connect = sqlite3.connect('bikesys.db')  # 服务器名，账户，密码，数据库名
        if self.connect:
            self.cursor = self.connect.cursor()
            # 获取列名
            self.cursor.execute(
                "pragma table_info('%s')" % self.tableName)
            self.columnsTmpList = self.cursor.fetchall()
            self.columnsList = self.columnsTmpList

            # 获取内容
            selectAllSql = "select * from " + self.tableName
            self.cursor.execute(selectAllSql)
            self.rowList = self.cursor.fetchall()  # 读取查询结果

            # 表格框
            self.root1 = Tk()  # 初始框的声明
            self.root1.config(bg=color1)
            self.root1.title('共享单车管理数据库系统')
            self.alignStr1 = clac_geo(self.root1, 500, 420)
            self.root1.geometry(self.alignStr1)
            treeView = self.readTreeViewAction(self.root1, self.rowList)

            # 增删改查的实现
            operationsFrame = LabelFrame(self.root1)
            operationsFrame.grid(row=1, column=0, sticky=NS)

            bt_insert = Button(operationsFrame, text='插入', bg=color1, fg=color2,
                               width=10, height=1, command=self.insert).grid(row=0, column=0)
            bt_delete = Button(operationsFrame, text='删除', bg=color1, fg=color2,
                               width=10, height=1, command=self.delete).grid(row=0, column=1)
            bt_update = Button(operationsFrame, text='更新', bg=color1, fg=color2,
                               width=10, height=1, command=self.update).grid(row=0, column=2)
            bt_select = Button(operationsFrame, text='查询', bg=color1, fg=color2,
                               width=10, height=1, command=self.select).grid(row=0, column=3)
            bt_flush = Button(operationsFrame, text='刷新', bg=color1, fg=color2,
                              width=10, height=1, command=self.flush).grid(row=0, column=4)
            bt_quit = Button(operationsFrame, text='退出', bg=color1, fg=color2,
                             width=10, height=1, command=self.quit).grid(row=0, column=5)

            self.cursor.close()
            self.connect.close()
            self.root1.mainloop()

        # 刷新

    def quit(self):
        msg = messagebox.askyesno("退出", "确认需要退出？");
        if (msg):
            exit()

    def flush(self):
        self.root1.destroy()
        self.DBShow(self.tableName)

        # 执行非选择语句

    def connOperation(self, sql):
        # 连接数据库
        connect = sqlite3.connect('bikesys.db')  # 服务器名，账户，密码，数据库名
        cursor2 = connect.cursor()  # 创建游标

        if connect:
            print(sql)
            cursor2.execute(sql)
            connect.commit()
            messagebox.showinfo(title='提示', message='操作成功!')
            cursor2.close()
            connect.close()
        else:
            messagebox.showinfo(title='提示', message='操作失败!')

        # 执行选择语句

    def connOperationSelect(self, sql):
        # 连接数据库
        connect = sqlite3.connect('bikesys.db')  # 服务器名，账户，密码，数据库名
        cursor2 = connect.cursor()  # 创建游标

        if connect:
            print(sql)
            cursor2.execute(sql)
            resList = cursor2.fetchall()
            messagebox.showinfo(title='提示', message='操作成功!')
            cursor2.close()
            connect.close()
            return resList
        else:
            messagebox.showinfo(title='提示', message='操作失败!')

        # 增

    def insert(self):
        window = Tk()
        window.title("插入")
        window.configure(bg=color1)
        window.geometry(clac_geo(window, 400, 400))
        frame = Frame(window, bg=color1)
        frame.pack(side='top', expand='yes')

        # 创建关联字符变量
        self.columnsVar = []
        for cNum in range(len(self.columnsList)):
            exec('self.var' + str(cNum) + ' = ' + 'StringVar')
            self.columnsVar.append('self.var' + str(cNum))

        # 创建标签组件
        self.entryList = []
        yValue = 0
        for i in range(len(self.columnsList)):
            label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10)
            # 创建文本框组件，同时设置关联的变量
            exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))")
            exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)")
            self.entryList.append("self.entryValue" + str(i))
            yValue += 1

        button_ok = Button(frame, text='插入', bg=color1, fg=color2,
                           width=10, height=1, command=self.insertGo).grid(row=yValue, column=0, sticky=W)
        button_quit = Button(frame, text='退出', bg=color1, fg=color2,
                             width=10, height=1, command=window.destroy).grid(row=yValue, column=1, sticky=E)

    def insertGo(self):
        for i in range(len(self.entryList)):
            self.entryList[i] = "'" + str(eval(self.entryList[i]).get()) + "'"
        values = ",".join(self.entryList)
        sql = "insert into %s values (%s)" % (self.tableName, values)
        self.connOperation(sql)

        # 删

    def delete(self):
        window = Tk()
        window.title("删除")
        window.configure(bg=color1)
        window.geometry(clac_geo(window, 400, 150))
        frame = Frame(window, bg=color1)
        frame.pack(side='top', expand='yes')
        # 创建关联字符变量
        varId1 = StringVar

        # 创建标签组件
        label = Label(frame, text="ID", bg=color1).grid(row=0, column=0)

        # 创建文本框组件，同时设置关联的变量
        ID = Entry(frame, textvariable=varId1)
        ID.grid(row=0, column=1)

        button_ok = Button(frame, text='删除', bg=color1, fg=color2, width=10, height=1,
                           command=lambda: self.deleteGo(ID.get())).grid(row=1, column=0, pady=20, sticky=W)
        button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1,
                             command=window.destroy).grid(row=1, column=1, pady=20, sticky=E)

    def deleteGo(self, ID):
        sql = "delete from %s where ID = '%s'" % (self.tableName, ID)
        self.connOperation(sql)

        # 改

    def update(self):
        window = Tk()
        window.title("更新")
        window.configure(bg=color1)
        window.geometry(clac_geo(window, 400, 400))
        frame = Frame(window, bg=color1)
        frame.pack(side='top', expand='yes')

        # 创建关联字符变量
        self.columnsVar = []
        for cNum in range(len(self.columnsList)):
            exec('self.var' + str(cNum) + ' = ' + 'StringVar')
            self.columnsVar.append('self.var' + str(cNum))

        # 创建标签组件
        self.entryList = []
        yValue = 0
        for i in range(len(self.columnsList)):
            label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10)
            # 创建文本框组件，同时设置关联的变量
            exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))")
            exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)")
            self.entryList.append("self.entryValue" + str(i))
            yValue += 1

        button_ok = Button(frame, text='更新', bg=color1, fg=color2,
                           width=10, height=1, command=self.updateGo).grid(row=yValue, column=0, sticky=W)
        button_quit = Button(frame, text='退出', bg=color1, fg=color2,
                             width=10, height=1, command=window.destroy).grid(row=yValue, column=1, sticky=E)

    def updateGo(self):
        itemsList = ['' for i in range(len(self.entryList))]
        for i in range(len(self.entryList)):
            itemsList[i] = eval(self.entryList[i]).get()
        tmpList = []
        for i in range(len(self.columnsList)):
            if itemsList[i]:
                tmpList.append(self.columnsList[i] + "='" + itemsList[i] + "'")
        reviseItems = ",".join(tmpList)
        sql = "update %s set %s where ID='%s'" % (self.tableName, reviseItems, itemsList[0])
        self.connOperation(sql)

        # 查

    def select(self):
        window = Tk()
        window.title("查询")
        window.configure(bg=color1)
        window.geometry(clac_geo(window, 400, 400))
        frame = Frame(window, bg=color1)
        frame.pack(side='top', expand='yes')

        # 创建关联字符变量
        self.columnsVar = []
        for cNum in range(len(self.columnsList)):
            exec('self.var' + str(cNum) + ' = ' + 'StringVar')
            self.columnsVar.append('self.var' + str(cNum))

        # 创建标签组件
        self.entryList = []
        yValue = 0
        for i in range(len(self.columnsList)):
            label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10)
            # 创建文本框组件，同时设置关联的变量
            exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))")
            exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)")
            self.entryList.append("self.entryValue" + str(i))
            yValue += 1

        button_ok = Button(frame, text='查询', bg=color1, fg=color2, width=10, height=1,
                           command=lambda: self.selectGo("='")).grid(row=yValue, column=0)
        button_ok = Button(frame, text='模糊查询', bg=color1, fg=color2, width=10, height=1,
                           command=lambda: self.selectGo(" like '")).grid(row=yValue, column=1)
        button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1,
                             command=window.destroy).grid(row=yValue, column=2)

    def selectGo(self, str1):
        itemsList = ['' for i in range(len(self.entryList))]
        for i in range(len(self.entryList)):
            print(eval(self.entryList[i]), type(eval(self.entryList[i])))
            itemsList[i] = eval(self.entryList[i]).get()
        tmpList = []
        for i in range(len(self.columnsList)):
            if itemsList[i]:
                tmpList.append(self.columnsList[i] + str1 + itemsList[i] + "' and ")
        reviseItems = "".join(tmpList)
        sql = "select * from %s where %s" % (self.tableName, reviseItems)
        sql = sql[:-4]

        resList = self.connOperationSelect(sql)
        if not resList:
            messagebox.showerror("警告", "查询结果为空！")
            return

        # 表格框
        root2 = Tk()  # 初始框的声明
        root2.title('查询结果')
        root2.geometry(self.alignStr1)
        treeview2 = self.readTreeViewAction(root2, resList)

        root2.mainloop()


if __name__ == '__main__':
    vp_start_gui()
